//
//  DatabaseManager.m
//  FMDBSingle
//
//  Created by qianfeng on 16/5/31.
//  Copyright © 2016年 qianfeng. All rights reserved.
//

#import "DatabaseManager.h"
#import "FMDB.h"
//数据库文件名
#define DATABASE_NAME @"TestData.db"
//表格名
#define TABLE_NAME @"Student"
@interface DatabaseManager()
@property (nonatomic,strong)FMDatabase *dataBase;

@end
@implementation DatabaseManager
#pragma mark -- 单利方法
+ (DatabaseManager *)sharedManager{
    static DatabaseManager *manager = nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken,^{
        manager = [[DatabaseManager alloc]init];
        
        //创建数据 和 表格
        [manager createDataBaseAndTable];
        
    });
    return manager;
}

#pragma mark -- 创建
//数据库和表格
- (void)createDataBaseAndTable{
   //1.数据库创建
    NSString *path = [NSString stringWithFormat:@"%@/Documents/%@",NSHomeDirectory(),DATABASE_NAME];
    NSLog(@"path:%@",path);
    
    _dataBase = [[FMDatabase alloc]initWithPath:path];
    
   if ([_dataBase open]) {
        //如果数据库打开 继续创建表格
       NSString *sql = [NSString stringWithFormat:@"create table if not exists %@(stuName varchar(20),stuAge int, stuSex varchar(2))",TABLE_NAME];
       if ([_dataBase executeUpdate:sql]) {
           NSLog(@"创建表格成功");
       }else{
           NSLog(@"创建表格失败");
       }
    }
    
    
}

#pragma mark -- 增
- (void)insertDataWithArray:(NSArray *)array{
    
    if (!array) {
        return;
    }
    for (int i = 0; i<array.count; i++) {
        Student *stu = array[i];
        NSString *sql = [NSString stringWithFormat:@"insert into %@ values ('%@',%@,'%@')",TABLE_NAME,stu.stuName,stu.stuAge,stu.stuSex];
        BOOL insert = [self.dataBase executeUpdate:sql];
        
        NSLog(@"insert result %d",insert);
    }
    
}

#pragma mark -- 删
- (void)deleteDataWithArray:(NSArray *)array{
    if (!array) {
        return;
    }
   
    for (int i = 0; i<array.count; i++) {
         Student *stu = array[i];
        NSString *sql = [NSString stringWithFormat:@"delete from %@ where stuName='%@' and stuAge=%@ and stuSex='%@'",TABLE_NAME,stu.stuName,stu.stuAge,stu.stuSex];
        [_dataBase executeUpdate:sql];
    }
}

#pragma mark -- 改
- (void)updataDataWithOld:(Student *)oldStu andNew:(Student *)newStu{
    if (!oldStu || !newStu ||[oldStu isEqual:newStu]) {
        return;
    }
    NSString *sql = [NSString stringWithFormat:@"uptate %@ set stuName='%@',stuAge=%@,stuSex='%@' where stuName='%@' and stuAge=%@ and stuSex='%@'",TABLE_NAME,newStu.stuName,newStu.stuAge,newStu.stuSex,oldStu.stuName,oldStu.stuAge,oldStu.stuSex];
    [_dataBase executeUpdate:sql];

}

- (NSArray *)selectAllDatas{
    NSMutableArray *dataArr = [NSMutableArray array];
    NSString *sql = [NSString stringWithFormat:@"select * from %@",TABLE_NAME];
    FMResultSet *set = [_dataBase executeQuery:sql];
    while ([set next]) {
        NSString *name = [set stringForColumn:@"stuName"];
        NSString *age = [set stringForColumn:@"stuAge"];
        NSString *sex = [set stringForColumn:@"stuSex"];
        NSDictionary *dic = @{@"stuName":name,@"stuAge":age,@"stuSex":sex};
        Student *stu = [Student createModelWithDic:dic];
        [dataArr addObject:stu];

    }
    return dataArr;
}




- (void)dealloc{
    [_dataBase close];
}


@end
